package com.dataconver.sync;

import com.dataconver.config.ConfigManager;
import com.dataconver.db.DatabaseManager;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.*;

/**
 * wm_mulity_route_his 增量同步
 * - 基于目标库 wm_user_rtu.imei 列表
 * - 从源库 MulityRoute_His 按 systime > last_sync_time 拉取增量
 * - 以源表 id 为唯一键在目标表 UPSERT，避免重复
 * - 同步完成后更新 wm_sync_status.last_sync_time
 */
public class IncrementalHisSyncStrategy implements SyncStrategy {
    private static final Logger logger = LoggerFactory.getLogger(IncrementalHisSyncStrategy.class);

    private final String sourceTable = "MulityRoute_His";         // 源表
    private final String targetTable = "wm_mulity_route_his";      // 目标表
    private final String keyField = "id";                          // 唯一键（使用源表主键id）
    private final String timestampField;                           // 增量时间字段（从配置文件读取）
    private final int batchSize;                                    // 批大小
    private final Map<String, String> columnMapping;                // 列映射（如 signel -> signel_level）

    public IncrementalHisSyncStrategy() {
        this.batchSize = ConfigManager.getInt("sync.batchSize", 1000);
        this.columnMapping = loadColumnMapping();
        // 从配置文件读取时间戳字段，默认为 systime
        this.timestampField = ConfigManager.getString("sync.table.wm_mulity_route_his.timestamp", "systime");
    }

    private Map<String, String> loadColumnMapping() {
        Map<String, String> mapping = new HashMap<>();
        try {
            String tableConfigKey = "sync.table." + targetTable + ".columnMapping";
            Map<String, Object> map = ConfigManager.getMap(tableConfigKey);
            if (map != null) {
                for (Map.Entry<String, Object> e : map.entrySet()) {
                    if (e.getKey() != null && e.getValue() != null) {
                        mapping.put(e.getKey(), String.valueOf(e.getValue()));
                    }
                }

            }
        } catch (Exception ex) {
            logger.warn("读取列名映射失败，使用默认映射", ex);
        }
        return mapping;
    }

    @Override
    public SyncResult execute(String tableName) {
        SyncResult result = new SyncResult();
        result.setTableName(targetTable);
        result.setStartTime(System.currentTimeMillis());
        try {
            // 1) 获取IMEI列表（目标库）
            List<String> imeis = loadImeisFromTarget();
            if (imeis.isEmpty()) {
                result.setSuccess(true);
                result.setEndTime(System.currentTimeMillis());
                return result;
            }

            // 2) 读取上次同步时间
            Timestamp lastSyncTime = loadLastSyncTime();
            
            // 检查当前状态表状态
            checkCurrentSyncStatus();

            // 3) 确保目标表存在并在 id 上建立唯一索引
            ensureTargetTableAndUniqueKey();

            // 4) 获取源表列列表
            List<String> sourceColumns = getSourceColumns();
            String upsertSql = buildUpsertSql(sourceColumns);

            // 5) 分批构造查询（IMEI IN (...) AND systime > ?），流式读写
            int processed = 0;
            int inserted = 0;
            int updated = 0;
            Timestamp maxSystime = null; // 初始化为null，用于记录本次同步的最大时间戳

            try (Connection srcConn = DatabaseManager.getSqlserverConnection();
                 Connection dstConn = DatabaseManager.getMysqlConnection()) {

                dstConn.setAutoCommit(false);

                for (int start = 0; start < imeis.size(); start += 1000) {
                    int end = Math.min(start + 1000, imeis.size());
                    List<String> batchImeis = imeis.subList(start, end);

                    StringBuilder select = new StringBuilder();
                    select.append("SELECT ");
                    for (int i = 0; i < sourceColumns.size(); i++) {
                        if (i > 0) select.append(", ");
                        select.append("[").append(sourceColumns.get(i)).append("]");
                    }
                    select.append(" FROM [").append(sourceTable).append("] WHERE ");
                    select.append("[").append("imei").append("] IN (");
                    for (int i = 0; i < batchImeis.size(); i++) {
                        if (i > 0) select.append(", ");
                        select.append("?");
                    }
                    select.append(")");
                    if (lastSyncTime != null) {
                        // 严格按照同步时间过滤
                        select.append(" AND [").append(timestampField).append("] > ?");
                    }
                    select.append(" ORDER BY [").append(timestampField).append("] ASC");

                    try (PreparedStatement ps = srcConn.prepareStatement(select.toString(), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {
                        int idx = 1;
                        for (String imei : batchImeis) {
                            ps.setString(idx++, imei);
                        }
                        if (lastSyncTime != null) {
                            // 直接使用同步时间，不做任何调整
                            ps.setTimestamp(idx++, lastSyncTime);
                            logger.info("设置查询时间参数: {}", lastSyncTime);
                        }
                        
                        // 添加详细的调试日志
                        logger.info("=== 主查询调试信息 ===");
                        logger.info("构建的SQL: {}", select.toString());
                        logger.info("查询参数 - IMEI数量: {}, lastSyncTime: {}", batchImeis.size(), lastSyncTime);
                        if (lastSyncTime != null) {
                            logger.info("时间过滤条件: [{}] > {}", timestampField, lastSyncTime);
                        }
                        logger.info("=====================");
                        
                        // 调试查询：检查源表中这些IMEI的数据情况（使用相同的时间过滤逻辑）
                        String debugSql = "SELECT [imei], [" + timestampField + "] FROM [" + sourceTable + "] WHERE [imei] IN (";
                        for (int i = 0; i < batchImeis.size(); i++) {
                            if (i > 0) debugSql += ", ";
                            debugSql += "'" + batchImeis.get(i) + "'";
                        }
                        debugSql += ")";
                        if (lastSyncTime != null) {
                            debugSql += " AND [" + timestampField + "] > '" + lastSyncTime + "'";
                        }
                        debugSql += " ORDER BY [" + timestampField + "] DESC";
                        
                        try (Statement debugStmt = srcConn.createStatement();
                             ResultSet debugRs = debugStmt.executeQuery(debugSql)) {
                            logger.info("调试查询: {}", debugSql);
                            int debugCount = 0;
                            while (debugRs.next() && debugCount < 10) {
                                String debugImei = debugRs.getString("imei");
                                Timestamp debugSystime = debugRs.getTimestamp(timestampField);
                                logger.info("调试结果 - IMEI: {}, {}: {}", debugImei, timestampField, debugSystime);
                                debugCount++;
                            }
                            if (debugCount == 0) {
                                logger.warn("调试查询返回0条记录，源表中可能没有这些IMEI的数据");
                            }
                        }
                        
                        try (ResultSet rs = ps.executeQuery();
                             PreparedStatement up = dstConn.prepareStatement(upsertSql)) {

                            // 添加主查询结果检查
                            logger.info("主查询开始执行，准备处理结果集...");
                            logger.info("主查询SQL: {}", select.toString());
                            logger.info("主查询参数: lastSyncTime={}", lastSyncTime);
                            int resultCount = 0;
                            int batchCount = 0;
                            
                            while (rs.next()) {
                                resultCount++;
                                // 记录最大systime
                                Timestamp st = rs.getTimestamp(timestampField);
                                if (st != null) {
                                    if (maxSystime == null || st.after(maxSystime)) {
                                        maxSystime = st;
                                    }
                                }

                                int p = 1;
                                for (String col : sourceColumns) {
                                    Object v = rs.getObject(col);
                                    up.setObject(p++, v);
                                }
                                up.addBatch();
                                batchCount++;
                                processed++;

                                if (processed % 1000 == 0) {
                                    logger.info("wm_mulity_route_his 已处理 {} 条", processed);
                                }

                                if (batchCount >= batchSize) {
                                    logger.info("执行批量插入，批次大小: {}", batchCount);
                                    int[] res = up.executeBatch();
                                    dstConn.commit();
                                    // 粗略统计：返回>=2视为更新（MySQL可能返回2）
                                    for (int r : res) {
                                        if (r > 1) updated++; else inserted++;
                                    }
                                    logger.info("批次执行完成，插入: {}，更新: {}", inserted, updated);
                                    batchCount = 0;
                                }
                            }
                            
                            logger.info("主查询结果集处理完成，共处理 {} 条记录", resultCount);

                            if (batchCount > 0) {
                                logger.info("执行最后一批插入，批次大小: {}", batchCount);
                                int[] res = up.executeBatch();
                                dstConn.commit();
                                for (int r : res) {
                                    if (r > 1) updated++; else inserted++;
                                }
                                logger.info("最后批次执行完成，插入: {}，更新: {}", inserted, updated);
                            }
                        }
                    }
                }

                dstConn.setAutoCommit(true);
            }



            result.setProcessedRows(processed);
            result.setTotalRows(processed);
            result.setInsertedRows(inserted);
            result.setUpdatedRows(updated);
            result.setSuccess(true);
            result.setEndTime(System.currentTimeMillis());


        } catch (Exception e) {
            logger.error("wm_mulity_route_his 增量同步失败", e);
            result.setSuccess(false);
            result.setErrorMessage(e.getMessage());
            result.setEndTime(System.currentTimeMillis());
        }

        return result;
    }

    private List<String> loadImeisFromTarget() throws SQLException {
        List<String> imeis = new ArrayList<>();
        String sql = "SELECT DISTINCT `imei` FROM `wm_mulity_route` WHERE `remark` IS NOT NULL";
        try (Connection conn = DatabaseManager.getMysqlConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                String imei = rs.getString(1);
                if (imei != null && !imei.trim().isEmpty()) {
                    imeis.add(imei.trim());
                }
            }
        }

        return imeis;
    }

    private Timestamp loadLastSyncTime() throws SQLException {
        String sql = "SELECT last_sync_time FROM wm_sync_status WHERE table_name = ?";
        try (Connection conn = DatabaseManager.getMysqlConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, targetTable);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    Timestamp ts = rs.getTimestamp(1);
                    if (ts != null) {
                        // 强制转换为北京时间
                        Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("Asia/Shanghai"));
                        cal.setTime(ts);
                        Timestamp beijingTime = new Timestamp(cal.getTimeInMillis());
                        
                        
                        return beijingTime;
                    }
                }
            }
        }
        // 若状态表无时间，则以目标表当前最大时间戳为基线，避免首次全量回灌
        String q = "SELECT MAX(`" + timestampField + "`) FROM `" + targetTable + "`";
        try (Connection conn = DatabaseManager.getMysqlConnection();
             PreparedStatement ps = conn.prepareStatement(q);
             ResultSet rs = ps.executeQuery()) {
            if (rs.next()) {
                Timestamp baseline = rs.getTimestamp(1);
                if (baseline != null) {
                    // 转换为北京时间显示
                    Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("Asia/Shanghai"));
                    cal.setTime(baseline);
                    Timestamp beijingBaseline = new Timestamp(cal.getTimeInMillis());
                    
        
                    return baseline; // 返回原始值，用于数据库查询
                }
            }
        }
        return null; // 目标表也为空，则允许首次全量
    }

    private void saveLastSyncTime(Timestamp ts) throws SQLException {
        String upsert = "INSERT INTO wm_sync_status(table_name,last_sync_time,updated_time) VALUES(?,?,NOW()) " +
                "ON DUPLICATE KEY UPDATE last_sync_time=VALUES(last_sync_time), updated_time=NOW()";
        

        
        try (Connection conn = DatabaseManager.getMysqlConnection();
             PreparedStatement ps = conn.prepareStatement(upsert)) {
            ps.setString(1, targetTable);
            ps.setTimestamp(2, ts);
            int rows = ps.executeUpdate();
    
            
            // 验证保存结果
            String verifySql = "SELECT last_sync_time, updated_time FROM wm_sync_status WHERE table_name = ?";
            try (PreparedStatement verifyPs = conn.prepareStatement(verifySql)) {
                verifyPs.setString(1, targetTable);
                try (ResultSet rs = verifyPs.executeQuery()) {
                    if (rs.next()) {
                        Timestamp savedTime = rs.getTimestamp("last_sync_time");
                        Timestamp updatedTime = rs.getTimestamp("updated_time");
                        
                        // 转换为北京时间显示
                        Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("Asia/Shanghai"));
                        cal.setTime(savedTime);
                        Timestamp beijingSavedTime = new Timestamp(cal.getTimeInMillis());
                        
                        cal.setTime(updatedTime);
                        Timestamp beijingUpdatedTime = new Timestamp(cal.getTimeInMillis());
                        

                    } else {
                        logger.warn("验证失败：未找到保存的时间戳记录");
                    }
                }
            }
        }
    }

    private List<String> getSourceColumns() throws SQLException {
        List<String> cols = new ArrayList<>();
        try (Connection conn = DatabaseManager.getSqlserverConnection()) {
            DatabaseMetaData md = conn.getMetaData();
            try (ResultSet rs = md.getColumns(null, null, sourceTable, null)) {
                while (rs.next()) {
                    cols.add(rs.getString("COLUMN_NAME"));
                }
            }
        }
        return cols;
    }

    private String buildUpsertSql(List<String> columns) {
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO `").append(targetTable).append("` (");
        for (int i = 0; i < columns.size(); i++) {
            if (i > 0) sql.append(", ");
            String targetColumn = columnMapping.getOrDefault(columns.get(i), columns.get(i));
            sql.append("`").append(targetColumn).append("`");
        }
        sql.append(") VALUES (");
        for (int i = 0; i < columns.size(); i++) {
            if (i > 0) sql.append(", ");
            sql.append("?");
        }
        sql.append(") ON DUPLICATE KEY UPDATE ");
        for (int i = 0; i < columns.size(); i++) {
            if (i > 0) sql.append(", ");
            String targetColumn = columnMapping.getOrDefault(columns.get(i), columns.get(i));
            sql.append("`").append(targetColumn).append("` = VALUES(`").append(targetColumn).append("`)");
        }
        return sql.toString();
    }

    private void ensureTargetTableAndUniqueKey() throws SQLException {
        try (Connection conn = DatabaseManager.getMysqlConnection()) {
            // 确保表存在（简单按源列创建可选）——这里假设表已存在
            // 确保 id 唯一索引
            String check = "SELECT COUNT(1) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name=? AND column_name='id' AND non_unique = 0";
            try (PreparedStatement ps = conn.prepareStatement(check)) {
                ps.setString(1, targetTable);
                try (ResultSet rs = ps.executeQuery()) {
                    boolean has = false;
                    if (rs.next()) has = rs.getInt(1) > 0;
                    if (!has) {
                        String idxName = "uk_" + targetTable + "_id";
                        String alter = "ALTER TABLE `" + targetTable + "` ADD UNIQUE KEY `" + idxName + "` (`id`)";
                        try (PreparedStatement a = conn.prepareStatement(alter)) {
                            a.executeUpdate();
                    
                        }
                    }
                }
            }
        }
    }

    private void checkCurrentSyncStatus() throws SQLException {
        String sql = "SELECT last_sync_time FROM wm_sync_status WHERE table_name = ?";
        try (Connection conn = DatabaseManager.getMysqlConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, targetTable);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    Timestamp lastSync = rs.getTimestamp(1);
                    if (lastSync != null) {
                
                    } else {
                        logger.warn("wm_sync_status.last_sync_time 为 null，可能需要进行全量同步");
                    }
                } else {
                    logger.warn("wm_sync_status 表中未找到 {} 的记录，可能需要进行全量同步", targetTable);
                }
            }
        }
    }

    @Override
    public String getStrategyName() {
        return "INCREMENTAL";
    }
}